import type { Router } from "express"
import type { Ioracle, IcallbackFn } from "../../types"

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getMailData", (req, res) => {
    const { date } = req.body
    let sql =
      " select to_date('" +
      date +
      "','yyyy-mm-dd') mail_date,a.server_name,a.mail_type, "
    sql += " nvl((select mail_result "
    sql += " from xx_server_manage_mail b "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += "    and rownum=1 "
    sql += " ),'應備份未備份') mail_result "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'1') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result1 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'2') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result2 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'3') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result3 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'4') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result4 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'5') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result5 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'6') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result6 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'7') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result7 "
    sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'8') from xx_server_manage_mail "
    sql +=
      " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    sql += " and trunc(receive_time)=to_date('" + date + "','yyyy-mm-dd') "
    sql +=
      " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    sql += " and rownum=1 ) result8 "
    sql += "    from xx_lpj_mail_setup a "
    sql += "   where 1=1 "
    sql += "    order by mail_type,mail_result "
    // let sql = "select a.server_name,a.mail_type,b.time,nvl(mail_result,'應備份未備份')mail_result, trunc(time) "
    // sql += " from xx_lpj_mail_setup a ,( "
    // sql += " select  a.*,upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7)) servers "
    // sql += " from xx_server_manage_mail a "
    // sql += " where (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ date +"','yyyy-mm-dd') "
    // sql += " ) b "
    // sql += " where a.server_name=b.servers(+) "
    //sql += " and  a. mail_type='AIP' "
    callbackFn( res, oracle.connCcapps,{sql})
  })

  router.post("/getSummaryData", (req, res) => {
    const { date } = req.body

    let sql =
      " begin xx_lpj_mail_summary_p(to_date('" + date + "','yyyy-mm-dd'));end; "
    oracle.connCcapps({sql}).then(_res => {
      if (_res.message === "success") {
        sql = " select to_char(server_date,'yyyy-mm-dd') server_date "
        sql += " ,server_name,mail_type,result_ok1,result_ng1,result_ok2 "
        sql += " ,result_ng2,result_ok3,result_ng3,result_ok4,result_ng4 "
        sql += " from xx_lpj_mail_temp a "
        sql += " where server_date = to_date('" + date + "','yyyy-mm-dd') "
        sql += " order by mail_type,server_name "
        callbackFn(res, oracle.connCcapps,{sql})
      } else {
        res.json({ code: 0, message: "error" })
      }
    }).catch((err) => {
      res.send({ code: 0, message: err })
    })
  })

  router.post("/getSummaryDataByOrg", (req, res) => {
    const { date,org_code } = req.body

    let sql =
      " begin xx_lpj_mail_summary_p(to_date('" + date + "','yyyy-mm-dd'));end; "
    oracle.connCcapps({sql}).then(_res => {
      if (_res.message === "success") {
        sql = " select to_char(server_date,'yyyy-mm-dd') server_date "
        sql += " ,a.server_name,mail_type,result_ok1,result_ng1,result_ok2 "
        sql += " ,result_ng2,result_ok3,result_ng3,result_ok4,result_ng4 "
        sql += " from xx_lpj_mail_temp a,xx_server_manage_header b "
        sql += " where server_date = to_date('" + date + "','yyyy-mm-dd') "
        sql += " and a.server_name = b.server_name"
        sql += " and b.org_code = '" + org_code + "'"
        sql += " order by mail_type,server_name "
        callbackFn(res, oracle.connCcapps,{sql})
      } else {
        res.json({ code: 0, message: "error" })
      }
    }).catch((err) => {
      res.send({ code: 0, message: err })
    })
  })

  router.post("/getDetailData", (req, res) => {
    const { query_type, server_name, server_date } = req.body
    let sql = " select * from ( "
    sql += " select xx_get_xtrstr_f(mail_context,'1') result1 "
    sql += " ,xx_get_xtrstr_f(mail_context,'2') result2 "
    sql += " ,xx_get_xtrstr_f(mail_context,'3') result3 "
    sql += " ,xx_get_xtrstr_f(mail_context,'4') result4 "
    sql += " ,xx_get_xtrstr_f(mail_context,'5') result5 "
    sql += " ,xx_get_xtrstr_f(mail_context,'6') result6 "
    sql += " ,xx_get_xtrstr_f(mail_context,'7') result7 "
    sql += " ,xx_get_xtrstr_f(mail_context,'8') result8 "
    sql += " ,xx_get_xtrstr_f(mail_context,'9') mail_result "
    sql += " ,receive_time "
    sql += " from xx_server_manage_mail "
    sql +=
      " where upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7)) = '" +
      server_name +
      "' "
    sql +=
      " and (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') ) "
    sql += " where 1 = 1 "
    if (query_type === 1) {
      sql += " and trunc(receive_time) = to_date('" + server_date + "','yyyy-mm-dd') "
      sql += " and mail_result = 'OK' "
    } else if (query_type === 2) {
      sql += " and trunc(receive_time) = to_date('" + server_date + "','yyyy-mm-dd') "
      sql += " and mail_result = 'NG' "
    } else if (query_type === 3) {
      sql +=
        " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 7 "
      sql += " and mail_result = 'OK' "
    } else if (query_type === 4) {
      sql +=
        " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 7 "
      sql += " and mail_result = 'NG' "
    } else if (query_type === 5) {
      sql += " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 14 "
      sql += " and mail_result = 'OK' "
    } else if (query_type === 6) {
      sql += " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 14 "
      sql += " and mail_result = 'NG' "
    } else if (query_type === 7) {
      sql +=
        " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 30 "
      sql += " and mail_result = 'OK' "
    } else if (query_type === 8) {
      sql +=
        " and trunc(receive_time) >= to_date('" + server_date + "','yyyy-mm-dd') - 30 "
      sql += " and mail_result = 'NG' "
    }
    
    // let sql = " select to_date('"+ server_date +"','yyyy-mm-dd') mail_date,a.server_name,a.mail_type, "
    // sql += " nvl((select mail_result "
    // sql += " from xx_server_manage_mail b "
    // sql += " where  (subject  like '%任务%' or subject  like '%任務%' or subject like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += "    and rownum=1 "
    // sql += " ),'應備份未備份') mail_result "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'1') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result1 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'2') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result2 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'3') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result3 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'4') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result4 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'5') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result5 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'6') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result6 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'7') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result7 "
    // sql += " ,(SELECT xx_get_xtrstr_f(mail_context,'8') from xx_server_manage_mail "
    // sql += " where  (subject  like '%任务%' or  subject  like '%任務%' or  subject  like '%Task%') "
    // sql += " and trunc(time)=to_date('"+ server_date +"','yyyy-mm-dd') "
    // sql += " and upper(substr(subject,instr(subject,'AIP at ')+7,instr(subject,':')-instr(subject,'AIP at ')-7))=a.server_name "
    // sql += " and rownum=1 ) result8 "
    // sql += " from xx_lpj_mail_setup a "
    // sql += " where 1=1 "
    // sql += " and a.mail_type = '"+ mail_type +"' "
    // sql += " and a.server_name = '"+ server_name +"' "
    // sql += " order by mail_type,mail_result "
    callbackFn(res, oracle.connCcapps,{sql})
  })

  // router.post("/getSummartData",(req,res) => {

  // })

  return router
}
